package together;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import javax.servlet.http.Cookie;

import misc.utility;

import arrays.ArrayListOps;

import com.chatSession;
import com.discussion;
import com.message;
import com.communication;
import com.project;

import com.sun.xml.internal.ws.encoding.soap.DeserializationException;

import strings.deserializer;
import database.databaseInteraction;
import database.database_auth;
import database.user;
import file.csv;
import file.file;
import groups.groups;
import strings.strings;

/*******************************************************************************
 * Software: TOGETHER BACKEND
 * Version:  1.4
 * StartDate: 2010_12_21_TIME_09:03:19
 * EndDtate: x > 2011_08_13_TIME_19:49:43

 * MANIFEST v1.2:

 * "The meaning of life is to be happy" (Dalailama of 2010)

 * I tend to believe that all deeds should serve the happiness of mankind... but man's in-perfection results in deeds that fail on the meaning of life, happiness.

 * We need to find new reasons to work together than money.

 * Or all we will do is enslave ourselves to the guy with the most money/oil/resources, for our very selfish ego.

 * this needs to change!

 * I am very grateful to the brave US citizens and all the others that helped end world war 2 and help to foster and preserve germany, instead of destroying it.

 * I believe we are now at a point, where not only the US people, but all good and open minded people on this planet desperately
 need to work together to find new ways of life that are fulfilled with happiness, sustainability, love, fun, advancer and fulfillment.

 We can do this, without killing each other over resources, if we stop beeing lazy and take responsibility how things are going in this world.

 * Everyday do we need to work hard to preserve the freedom and peace that our ancestors have given their lives for.

 * Everyday happiness is what this life is ALL about. Not money. Or war. Or power. Or honor.

 * This platform is intended to make the world a better, happier, transparent and democratic place for all living creatures.

 * You can install it on your own home-server and only allow family members access and upload your holiday pictures.
 * or run it on an rented amazon server in the cloud.
 * or connect it to all the other platforms that run out there to exchange data and information without limitations.
 * As always: some people provide the technology, how it is used, is totally up to the user. (good or evil ways)

 * It's intention is:

 1. to be simple
 "TECHNOLOGY THAT MAKES US HAPPY, IS SIMPLY SIMPLE IN EVERY KIND OF WAY!"
 Why is simplicity so important?
 because simplicity results in less errors, less errors result in higher quality software, higher quality software results in happier users. 

 2. give common people back their freedom and independence. (of speech and uncensored communication and self-organization)
 A flash.swf is more binary-closed source than facebook-html or any html output from a server and therefore better to protect your privacy, because a flash.swf's content will (hopefully and despite the announcements) never be parse-able and indexable and searchable by google. keeping your private profile (almost) perfectly safe.

 3. to give common people a tool to self-organize themselves and share informations in a peaceful and productive way
 to escape the dependencies that lead to exploitation and slavery by money, industries and governments

 4. to give common people a tool of communication and finding people to cooperate with to make this world better not only for themselves.

 5. to solve problems that would need a massive lobby, which does not exist, because its not profitable,
 and therefore solve problems on an community-based common-people-form-their-own-lobby-and-help-eath-other way.

 For example: research
 Research is (in 99.9%) sponsored by industries and therefore not independent.

 Independent research e.g. in the fields of how to heal and prevent cancer would be greatly needed.

 Because there is such a massive lack of proofed informations and corrupt disinformation, leaving not only patients
 but also doctors completely "in the dark".

 There is no patent possible on broccoli, but if broccoli would help, the industry would never tell you and never fund
 any scientific research and studies in that field. this needs to change.

 Otherwise we have middle-ages information-politics where the educated church-members (scientists and industries)
 can easily use and misuse common people for "which hunt" (of educated critics)
 and exploitation
 and slavery of the common people.
 (slavery is a very profitable business-model and still exists in western states, just more indirect and hidden)

 those that know, enslave those that don't.

 5. to self-govern the educated common people to help each other to gain back independence and freedom.

 6. to be as modular and open source as possible, it shall be possible and well documented how to write your own module
 for this platform and make your ideas and dreams come true.

 * PURPOSE OF THIS FILE:
 this is the central mxml which links all the modules and classes together in one program.
 if you want your module to be included in the program, this file needs to be changed.
 (just check out how i did it with my modules and then copy and paste and rename everything)

 * SETUP:
 actually this platform will be distributed as a vm-ware and xen and maybe kvm virtual machine only.
 (you can put together the source code pieces (java server, flash client) yourself, because
 it's all on google code:
 
 http://code.google.com/p/together/
 
 # Non-members may check out a read-only working copy anonymously over HTTP.
 svn checkout http://together.googlecode.com/svn/trunk/ together-read-only 
 
 but this is for hardcore techies(cumbersome))
 
 so the setup process is a matter of getting this virtual machine to work. (which should be much faster and easier than
 manual installing all the requirements for this platform)

 if you want to run on real hardware directly please remind me to make a proper howto-setup-video of the together platform on youtube.

 * REQUIREMENTS:
	 1. on the backend mysql-server is required containing a database "together" with a table format that can be downloaded 
	 here: (remind me if the link is missing)
	 2. tomcat-java-webserver with together-backend (together-backend are java classes (main class is TogetherXML.java) that are also open source) 

 * USAGE EXAMPLE:
	 create a new project in flash/flex builder using 3.5 sdk. (some components of my sdk is currently not 4.0 compatible, sorry for that)
	 extract all the flex-sources in that project folder.
	
	 open this TOGETHER.mxml file in flash/flex builder,
	 and compile it as a flash program. (r-Click on this source code -> Debug As -> Web Application)

 * LICENSE:  GNU General Public License (http://www.gnu.org/licenses/gpl.html) 	 *  	 * English: 		This program is free software; 		you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; 		either version 3 of the License, or (at your option) any later version.   		This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 		without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 		See the GNU General Public License for more details. 		You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>.   		 * Excerpt English: 		 * Our General Public Licenses are designed to make sure that you have the freedom to distribute copies of free software (and charge for them if you wish), 		 * that you receive source code or can get it if you want it, that you can change the software or use pieces of it in new free programs, and that you know you can do these things   		Auf Deutsch: 		Dieses Programm ist freie Software. Sie knnen es unter den Bedingungen der GNU General Public License, wie von der Free Software Foundation verffentlicht, 		weitergeben und/oder modifizieren, entweder gemss Version 3 der Lizenz oder (nach Ihrer Option) jeder spteren Version. 		Die Verffentlichung dieses Programms erfolgt in der Hoffnung, dass es Ihnen von Nutzen sein wird, aber OHNE IRGENDEINE GARANTIE, 		sogar ohne die implizite Garantie der MARKTREIFE oder der VERWENDBARKEIT FR EINEN BESTIMMTEN ZWECK. 		Details finden Sie in der GNU General Public License. 		Sie sollten ein Exemplar der GNU General Public License zusammen mit diesem Programm erhalten haben. 		Falls nicht, siehe <http://www.gnu.org/licenses/>.   		 * Auszug Deutsch: 		 * Wenn wir von freier Software sprechen, so beziehen wir uns auf Freiheit, nicht auf den Preis. 		 * Unsere Allgemeinen ffentlichen Lizenzen sind darauf angelegt, sicherzustellen, dass Sie die Freiheit haben, 		 * Kopien freier Software zu verbreiten (und dafr etwas zu berechnen, wenn Sie mchten), die Mglichkeit, 		 * dass Sie die Software als Quelltext erhalten oder den Quelltext auf Wunsch bekommen, dass Sie die Software 		 * ndern oder Teile davon in neuen freien Programmen verwenden drfen und dass Sie wissen, dass Sie dies alles tun drfen.   GNU General Public License (http://www.gnu.org/licenses/gpl.html)

 * FEATURES:
	 MODULE_chat:
	 WHAT? the chat module should be ready in a few hours.
	 a cool feature is that it will have sub-topics, meaning: if you discuss a hot topic of an incredible important problem and finding a perfect democratic solution is a process of iterating and discussing over every sub-topic until all the communication partners agree on all the sub-topics and finally on a solution for the main-topic can be found in an topic->subtopic->topic way.
	 in a later stage... the whole discussion should be viewable as a tree of discussion-message-lists to get a good overview over the process and see how intermediate discussion results lead to the final conclusion. 
	
	 FINISHED?: the chat module should be ready in a few hours.
	
	 MODULE_register:
	 WHAT? enable users to register.
	 FINISHED?: should work.
	
	 * TODO: XXX:
	 * SECURITY:
		 * because of security reasons..... it would be recommended to use only https! and send username + password for every transaction!
		 * this should disable the possibility of sending a custom crafted http request to the server and reading the whole database with one transaction!
		 * before sending the password via https it should be encrypted again... just to make sure no ears dropping can be done in any way... well key logger always work i guess.
		
	
	 * PURPOSE:
		 * flex <-XML-> java <-> MySQL
		 * TODO: o not transmitting password after successfull login to client.
		 * TODO: o when it comes to security, currently the password for the dataBase is send via http(not even https)
		 * to the server... and can therefore (probably) easily captured or maybe even decrypted from the binary .swf
		 * probably best way would be to always send the loggedInUser username and password with every request
		 * and then "unlock" the server-stored database-password stored on the server side.
		 * the mysql database username and password will be in a file called TogetherXML.config
		 * (/Volumes/DATA/JAVA/SETUP/ECLIPSE/OSX/32BIT/CARBON/eclipse-jee-galileo-SR2-macosx-carbon/Eclipse_JEE.app/Contents/MacOS/TogetherXML.config)
		 * if the file does not exist. a default layout will be created.
		 * 
	 * FEATURES:
		 * translate between flash and mysql via java.
		 * 
	 * USAGE?
	 * 
	 * 
 * CHANGELOG / CREDITS:
 * WHO?							| WHAT?															| PRECIOUS TIME SPEND?	| WHY?
 * 								| insertIfNotExists and register in separate functions			| 2h					| insertIfNotExists was hard-linked to the usage as registration of a new user.
 *  							| poll returns now list of all users without passwords			| 2h					| why not? answer.append("<record><allUsers>"+allUsers+"</allUsers></record>\n");

 Footnotes:
 STATUS TAGS:

 UT  = UnTested
 (its not fun figuring out what others did wrong)

 TWD = Tested Without Documentation
 -> it is tested by the programmer, but the test case is not written down in documentation, this will make it hard for other people to re-test the whole program.
 (okay method but prone to errors)

 TAD = Tested and Documented
 -> it is tested and the test case is also documented in the test-documentation, so that it can be re-tested when the whole program changes
 (currently best known method)
 * 
 *******************************************************************************/
/**
 * Servlet implementation class TogetherXML
 */
@WebServlet(name = "togetherxml", urlPatterns = { "/togetherxml" })
public class TogetherXML extends HttpServlet {

	private static final long serialVersionUID = 1L;

	/** class instances **/
	public databaseInteraction DatabaseConnection = null;
	public utility utility_inst = new utility();
	public user user_inst = new user();
	public groups groups_inst = new groups();
	public ArrayListOps ArrayListOps_inst = new ArrayListOps();
	public strings strings_inst = new strings();
	public deserializer deserializer_inst = null;
	public strings string_inst = new strings();
	public project project_inst = null;
	public discussion discussion_inst = null;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public TogetherXML() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		PrintWriter writer = response.getWriter();
		StringBuffer output = new StringBuffer();
		output.append("<html><body><h3>welcome to the flex<-java->mysql endpoint. call this endpoint from your flex application.</h3></body></html>");
		writer.println(output);
		writer.close();
	}

	/** main variables **/
	// hashmap / object to which properties can be added dynamically
	public HashMap parameters = new HashMap();
	public HashMap LINES = null;

	/** serialized data as string/text from http request **/
	public String data = "";

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException
	{
		DatabaseConnection = new databaseInteraction();

		response.setContentType("text/xml;charset=UTF-8");
		PrintWriter writer = response.getWriter();

		// System.out.println("Query String:");
		// System.out.println(request.getQueryString()); // returns null

		// read http request arguments/parameters
		java.util.Enumeration enumeration = request.getParameterNames();
		while (enumeration.hasMoreElements()) {
			String name = (String) enumeration.nextElement();
			String value = (String) request.getParameter(name);

			// System.out.println(name+":"+value);
			parameters.put(name, value); // adding value into HashMap
		}

		// initialize deserializer_inst with some data on how to break up the
		// request-data and translate it into java-objects
		deserializer_inst = new deserializer(parameters);
		LINES = deserializer_inst.deserialize(parameters);

		/**
		 * connectionParameters from flex: HTTPSERVICE_XML.request = Object
		 * (@1811ce51) action = "readTable" class_name = "ListeOnline"
		 * DatabaseName = "listeonline" dataEncoding = "xml" host = "localhost"
		 * packet_size = 300 [0x12c] password = "" port = "3306" SortField = ""
		 * SortMode = "ASC" TableName = "users" type = "mysql" url =
		 * "http://localhost:8080/ListeOnline/ListeOnline" user = "root"
		 **/

		StringBuffer output = new StringBuffer();
		StringBuffer answer = new StringBuffer();
		output.append("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n<sqlxml>\n");

		/**
		 * what command the backend shall execute
		 **/
		String action = (String) parameters.get("action");

		/** what servlet-class should respond to the action **/
		String class_name = (String) parameters.get("class_name");

		/** serialized data as string/text **/
		data = (String) parameters.get("data"); //

		/** what data format is used to transmit: xml, amf **/
		String dataEncoding = (String) parameters.get("dataEncoding");

		/** put auth stuff in one single object for easier handling **/
		database_auth database_auth_inst = new database_auth(); // will be read from configuration file TogetherXML.config -> host,port,DatabaseName,type,TableName,user,password file can be found @ /Volumes/DATA/JAVA/SETUP/ECLIPSE/OSX/32BIT/CARBON/eclipse-jee-galileo-SR2-macosx-carbon/Eclipse_JEE.app/Contents/MacOS/TogetherXML.config

		/** what kind of database is used possible values: mysql **/
		String type = database_auth_inst.type; // (String)
												// parameters.get("type");

		/** what default database to read/work on, can be overwritten by request data **/
		String DatabaseName_temp = (String) parameters.get("DatabaseName");
		if(DatabaseName_temp.equals(""))
		{
			DatabaseName_temp = database_auth_inst.DatabaseName; // (String)
		}
		else
		{
			database_auth_inst.DatabaseName = DatabaseName_temp;
		}

		/** what table to read/work on
		 * needs to come from request, unless it has something to do with user-management then 
		 * database_auth_inst.authTable from endpoiont_xml.config shall be used. 
		 * **/
		String TableName = (String) parameters.get("TableName");
		
		/** username to database **/
		String user = database_auth_inst.user; // (String) parameters.get("user");

		/** password to database **/
		String password = database_auth_inst.password; // (String) parameters.get("password");

		/**
		 * wether to use password encryption or not 1 = yes 0 = no
		 * **/
		String passwordEncryption = database_auth_inst.passwordEncryption;

		/**
		 * url to the host e.g. localhost or ip adress or domain name without
		 * http://
		 **/
		String host = database_auth_inst.host; // (String)
												// parameters.get("host");

		/** port where database is reachable **/
		String port = database_auth_inst.port; // (String)
												// parameters.get("port");

		/** in what chunks the data should be procesed & transmitted **/
		String packet_size = (String) parameters.get("packet_size");

		/**
		 * if the action is a read operation, in what sort the elements should
		 * be returned
		 **/
		String SortField = (String) parameters.get("SortField");
		/** sortmode: can be ASC or DSC (ascending / descending) **/
		String SortMode = (String) parameters.get("SortMode");
		/** complete url to servlet **/
		String url = (String) parameters.get("url");
		/** from is only in use when action equals readRange **/
		String from = (String) parameters.get("from");
		/** amount is only in use when action equals readRange **/
		String amount = (String) parameters.get("amount");
		/**
		 * resultFormat -> in what format the result should be (default is xml)
		 * possible values: xml -> return data as flex digestable xml java -> no
		 * xml fency just the values, so that java can process them further.
		 **/
		String mode = "xml";

		/** temp variable where the mysql query is stored **/
		String question = "";

		/** instantiate all classes that depend on database_auth_inst **/
		project_inst = new project(database_auth_inst);
		discussion_inst = new discussion(database_auth_inst);

		/**
		 * has many core communication functionalities
		 * 1. return a list of all users
		 * 1.1. report on/offline status of user offline is defined as: no polling since 1minute...
		 * then user is marked "offline" by the system.
		 * 
		 * 2. update user / current session informations: ping, groups, lastlogin, status, loggedIn etc.
		 * 
		 * 3. mark read messages readMessages as read
		 * 
		 * 4. check for new messages and deliver them if they are marked "new" or "offline"
		 * 
		 * 5. save session: status of what windows are open and at what position, what discussion was last chatted about... whith who... etc.
		 * 
		 * 6. determine ping time
		 */
		if (action.equals("poll")) {
			boolean result = false;
			String status = "";

			// var DATA:Object =
			// {loggedIn:singleton_storage.singleton().user.loggedIn,status:singleton_storage.singleton().user.status};
			Set<String> KEYSET = LINES.keySet();
			Object[] KEYS = KEYSET.toArray();

			String KEYS_AND_VALUES = "";

			String USERID = "";
			String IDlastReceivedMessage = "";
			String readMessages = "";
			String loginUser = "";
			String groupsOfUser = ""; // all groups that the user belongs to, makes it easier to retrieve the new messages for this group
			String loginPassword = "";
			String pingRequestSendTime = "0"; // send this back to client so
												// client can calculate
												// roundtrip time

			/** iterate over lines **/
			for (int counter = 0; counter < KEYS.length; counter++) {
				// keys & values
				HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
				for (String KEY : LINE.keySet()) {
					if (KEY.equals("ID")) {
						USERID = LINE.get(KEY);
					} else if (KEY.equals("username")) {
						loginUser = LINE.get(KEY);
					} else if (KEY.equals("groupsOfUser")) {
						groupsOfUser = LINE.get(KEY);
					} else if (KEY.equals("password")) {
						loginPassword = LINE.get(KEY);
					} else if (KEY.equals("pingRequestSendTime")) {
						pingRequestSendTime = LINE.get(KEY);
						if (pingRequestSendTime.equals("NaN")) {
							pingRequestSendTime = "0";
						}
						KEYS_AND_VALUES += "`ping`=" + "'" + LINE.get(KEY)
								+ "',";
					} else if (KEY.equals("IDlastReceivedMessage")) {
						IDlastReceivedMessage = LINE.get(KEY);
					} else if (KEY.equals("readMessages")) {
						readMessages = LINE.get(KEY);
					} else {
						//
						KEYS_AND_VALUES += "`" + KEY + "`=" + "'"
								+ LINE.get(KEY) + "',";
					}
				}
			}

			KEYS_AND_VALUES = new String(KEYS_AND_VALUES.substring(0,
					KEYS_AND_VALUES.length() - 1)); // remove last ,

			// 0. check if the user exists
			if (user_inst.doesUserExist(loginUser, loginPassword,passwordEncryption, database_auth_inst))
			{
				if (utility_inst.empty(IDlastReceivedMessage))
				{
					IDlastReceivedMessage = "0";
				}
				/**
				 * 1. return a list of all users
				 * 1.1. report on/offline status of user offline is defined as: no polling since 1minute...
				 * then user is marked "offline" by the system.
				 **/
				StringBuffer allUsers = user_inst.allUsersXML(loginUser, loginPassword, passwordEncryption, database_auth_inst);
				answer.append("<record><allUsers>"+allUsers+"</allUsers></record>\n");

				// 2. update user / current session informations: ping, groups, lastlogin, status, loggedIn etc.
				question = "UPDATE `"+database_auth_inst.authTable+"` SET "+KEYS_AND_VALUES+" WHERE `"+database_auth_inst.authTable + "`.`ID` = "+USERID+";"; // TableName
				// UPDATE `users` SET
				// `chatUserGroups`='favouriteUsers=user4,user5,user;blockedUser=user1,user2,user;',`status`='',`lastlogin`='1293150291168',`chatdiscussions`='',`loggedIn`='1',`openWindows`='',`chatUsers`='',`group`='',`ping`='0'
				// WHERE `users`.`ID` = 1;

				DatabaseConnection.askDatabase(database_auth_inst, question);

				// * 3. mark read messages readMessages as read

				communication com_inst = new communication();
				
				Pattern MessageIDSEPARATOR = Pattern.compile(":");
				if(!readMessages.isEmpty())
				{
					String[] MessageIDs = MessageIDSEPARATOR.split(readMessages);
				    for(Integer counter=0; counter<MessageIDs.length; counter++)
				    {
				    	com_inst.updateMessage(MessageIDs[counter],"status","read",database_auth_inst);
				    }
				}
				
				// * 4. check for new messages and deliver them if they are marked "new" or "offline"

				question = "SELECT * FROM `communication` WHERE `toUserID` = "+USERID+" AND `status` = 'unread' AND `status` != 'deleted' AND `ID` > "+IDlastReceivedMessage+";";
				// table communication: ID MESSAGEID subject discussionID
				// discussionParent fromUserID toUserID toGroup timeSend status
				// message spam

				answer.append("<record><toUser>"+DatabaseConnection.askDatabase(database_auth_inst,question)+"</toUser></record>\n");

				// 3.1. check in what groups the user is -> get all messages for
				// that group
				// get all groups that the user is in
				ArrayList<Object> GroupIDs = groups_inst.UserIsMemberOfGroups(USERID, database_auth_inst);
				HashMap<String, String> GROUP = null;
				String WHERE = "WHERE (";

				if (GroupIDs.size() == 0)
				{
					status = "user is not in any group";
				}
				else
				{
					for (Integer counter = 0; counter < GroupIDs.size(); counter++)
					{
						GROUP = (HashMap<String, String>) GroupIDs.get(counter);
						// System.out.println(GROUP);
						// System.out.println(GROUP.get("ID"));

						WHERE += "`toGroupID` = " + GROUP.get("ID") + " OR ";
					}

					WHERE = string_inst.removeLastCharX(WHERE, 4);
					
					// put it into brackets
					WHERE = WHERE+")";

					// 3. check for new messages for the groups the user is in
					question = "SELECT * FROM `communication` "+WHERE+" AND `status` = 'unread' AND `status` != 'deleted' AND `ID` > "+IDlastReceivedMessage+";";
					// table communication: ID MESSAGEID subject discussionID
					// discussionParent fromUserID toUserID toGroup timeSend
					// status message spam

					answer.append("<record><toGroup>"+DatabaseConnection.askDatabase(database_auth_inst, question)+"</toGroup></record>\n");
				}

				// 3.2. check in what discussion the user is -> get all messages
				// for that discussion
				// get all discussions that the user is in
				ArrayList<Object> DISCUSSIONIDs = discussion_inst.UserDiscussionSubscriptions(USERID, database_auth_inst);

				HashMap<String, String> DISCUSSION = null;

				WHERE = "WHERE ";

				if (DISCUSSIONIDs.size() == 0)
				{
					status = "user is not in any discussion";
				}
				else
				{
					for (Integer counter = 0; counter < DISCUSSIONIDs.size(); counter++)
					{
						DISCUSSION = (HashMap<String, String>) DISCUSSIONIDs.get(counter);
						System.out.println(DISCUSSION);
						System.out.println(DISCUSSION.get("DISCUSSIONID"));

						WHERE += "`toDiscussionID ` = "
								+ DISCUSSION.get("DISCUSSIONID") + " OR ";
					}

					WHERE = string_inst.removeLastCharX(WHERE, 4);

					// 3. check for new messages for the groups the user is in
					question = "SELECT * FROM `communication` "
							+ WHERE
							+ " AND `status` = 'unread' AND `status` != 'deleted' AND `ID` > "
							+ IDlastReceivedMessage + ";";
					// table communication: ID MESSAGEID subject discussionID
					// discussionParent fromUserID toUserID toGroup timeSend
					// status message spam

					answer.append("<record><toDiscussion>"
							+ DatabaseConnection.askDatabase(
									database_auth_inst, question)
							+ "</toDiscussion></record>\n");
				}

				// 3.3. check in what projects the user is -> get all messages
				// for that project
				// get all discussions that the user is in
				ArrayList<Object> PROJECTIDs = project_inst.UserProjects(USERID, database_auth_inst);
				HashMap<String, String> PROJECT = null;
				WHERE = "WHERE ";

				if(PROJECTIDs.size() == 0)
				{
					status = "user is not in a team of any project";
				}
				else
				{
					for (Integer counter = 0; counter < PROJECTIDs.size(); counter++)
					{
						PROJECT = (HashMap<String, String>) PROJECTIDs.get(counter);
						System.out.println(PROJECT);
						System.out.println(PROJECT.get("PROJECTID"));
	
						WHERE += "`toProjectID` = " + PROJECT.get("PROJECTID")+ " OR ";
					}

					WHERE = string_inst.removeLastCharX(WHERE, 4);

					// 3. check for new messages for the groups the user is in
					question = "SELECT * FROM `communication` "
							+ WHERE
							+ " AND `status` = 'unread' AND `status` != 'deleted' AND `ID` > "
							+ IDlastReceivedMessage + ";";
					// table communication: ID MESSAGEID subject discussionID
					// discussionParent fromUserID toUserID toGroup timeSend
					// status message spam

					answer.append("<record><toProject>"+ DatabaseConnection.askDatabase(database_auth_inst, question)+ "</toProject></record>\n");
				}

				// 3.4. check for new chat seeions -> get session
				question = "SELECT * FROM `chat` WHERE `toUserID` = '"+USERID+"';";
				answer.append("<record><ChatSessions>"+ DatabaseConnection.askDatabase(database_auth_inst, question)+ "</ChatSessions></record>\n");

				// 4. send back ping request time
				// table users: ID username password email created deleted
				// lastlogin logincount guestbook selfdescription status
				// wishlist hopes dreams pathToFiles enabled loggedIn
				// openWindows ping
				answer.append("<record><pingRequestSendTime><![CDATA["+ pingRequestSendTime+ "]]></pingRequestSendTime></record>\n");
				output.append(answer);
			}
		}

		/**
		 * this function writes one record per message
		 * 
		 * it is not good for chat because for chats its better to have one record per "session"
		 * 
		 * handles the delivery of chat messages
		 * 
		 * how this works:
		 * 
		 * when user A writes to user B a new chat-session-record with a ChatSessionID A:B is written
		 * into table chat
		 * 
		 * in this record the whole chat session is written in this format:
		 * 
		 * userName:chatMessage#
		 * userName:chatMessage#
		 * userName:chatMessage#userName:chatMessage#userName:chatMessage#userName:chatMessage#
		 * 
		 * during polling it is always compared
		 *     how much chat-messages (chat session split by #) of chat are in the database
		 * and how much chat-messages of chat are displayed to the client
		 * 
		 * if the amount of chat-messages in the database is not the amount of chat-messages
		 * displayed to the user than the server automatically answers the poll
		 * with the missing chat messages.
		 * 
		 * thats the plan now lets see how well this works.
		 **/
		if (action.equals("chat"))
		{
			// 1. check if the user exists
			// if(user_inst.doesUserExist(loginUser, loginPassword,
			// passwordEncryption, database_auth_inst))

			communication com_inst = new communication();

			// LINES = deserializer_inst.deserialize(parameters);

			// String data = (String) parameters.get("data");
			String loginUser = (String) parameters.get("loginUser");
			String loginPassword = (String) parameters.get("loginUser");
			String pingRequestSendTime = "0"; // send this back to client so
												// client can calculate
												// roundtrip time

			Set<String> KEYSET = LINES.keySet();
			Object[] KEYS = KEYSET.toArray();

			HashMap<String, String> MESSAGE_HASH = (HashMap) LINES.get(0); // usually only one message is send at one time
			
			chatSession CHATSESSION_inst = new chatSession(database_auth_inst);

			// message fromUserID toUserID status discussionParent subject
			// timeSend toGroup
			// {username=username, status=, ID=1, lastlogin=1302299541000,
			// loggedIn=1, openWindows=, IDlastReceivedMessage=4,
			// password=asdfjkl123!, pingRequestSendTime=1302299726888,
			// ping=1709}
			// System.out.println(MESSAGE_HASH.get("message"));
			CHATSESSION_inst.text = MESSAGE_HASH.get("text");
			// System.out.println(MESSAGE_HASH.get("fromUserID"));
			CHATSESSION_inst.setfromUserID(MESSAGE_HASH.get("fromUserID"));
			// System.out.println(MESSAGE_HASH.get("toUserID"));
			CHATSESSION_inst.settoUserID(MESSAGE_HASH.get("toUserID"));
			// System.out.println(MESSAGE_HASH.get("toGroupID"));
			CHATSESSION_inst.toGroupID = MESSAGE_HASH.get("toGroupID");
			// System.out.println(MESSAGE_HASH.get("toDiscussionID"));
			CHATSESSION_inst.toDiscussionID = MESSAGE_HASH.get("toDiscussionID");
			// System.out.println(MESSAGE_HASH.get("toProjectID"));
			CHATSESSION_inst.toProjectID = MESSAGE_HASH.get("toProjectID");
			// System.out.println(MESSAGE_HASH.get("status"));
			CHATSESSION_inst.status = MESSAGE_HASH.get("status");
			// System.out.println(MESSAGE_HASH.get("spam"));
			CHATSESSION_inst.spam = MESSAGE_HASH.get("spam");
			// System.out.println(MESSAGE_HASH.get("discussion"));
			CHATSESSION_inst.DISCUSSION = new discussion(database_auth_inst, CHATSESSION_inst);
			CHATSESSION_inst.DISCUSSION.discussionParentID = MESSAGE_HASH.get("discussionParentID");
			// System.out.println(MESSAGE_HASH.get("subject"));
			CHATSESSION_inst.subject = MESSAGE_HASH.get("subject");
			// System.out.println(MESSAGE_HASH.get("TimeStarted"));
			CHATSESSION_inst.TimeStarted = MESSAGE_HASH.get("TimeStarted");
			// System.out.println(MESSAGE_HASH.get("TimeFinished"));
			CHATSESSION_inst.TimeFinished = MESSAGE_HASH.get("TimeFinished");
			// System.out.println(MESSAGE_HASH.get("TimeLastMessage"));
			CHATSESSION_inst.TimeLastMessage = MESSAGE_HASH.get("TimeLastMessage");
			// System.out.println(MESSAGE_HASH.get("ChatSessionID"));
			CHATSESSION_inst.ChatSessionID = MESSAGE_HASH.get("ChatSessionID");

			// 1. check if the sending user's credentials fit
			if(user_inst.doesUserExist(loginUser, loginPassword,passwordEncryption, database_auth_inst))
			{
				// 2. send message to user/group/project/discussion
				answer = com_inst.sendChatMessage(CHATSESSION_inst, database_auth_inst);
				// answer.append("<record><status>success</status></record>\n");
				// answer.append("<record><status>message delivered successfuly</status></record>\n");
				output.append(answer);
			}
			else
			{
				answer.append("<record><status>error</status></record>\n");
				answer.append("<record><status>can not send message, user does not exist</status></record>\n");
				output.append(answer);
			}
		}
		
		/**
		 * this function writes one record per message
		 * 
		 * it is not good for chat because for chats its better to have one record per "session"
		 * 
		 * if you want a chat use function "chat" instead
		 * 
		 * handles the delivery of a messages
		 * 
		 * how this works: basically for every user (groups are just
		 * accumulations of users) a record will be written to table
		 * communication.
		 * 
		 * this makes it easy to retrieve all messages for a given user.
		 * 
		 * // get only all messages for toUserID 1
		   SELECT * FROM `communication` * WHERE `toUserID` = 1
		 * 
		 * // get only new messages for toUserID 1
		   SELECT * FROM `communication` WHERE `toUserID` = 1 AND `read` = 0
		 * 
		 * 0. translate groups/discussion subscribers/subdiscussions subscribers
		 * to a list/HashMap of users 1. does the receiver exist? no: don't
		 * write a record yes: 2. is the receiver ignoring the source? yes:
		 * don't write a record no: 3. write a record with receiverUser with the
		 * username of the receiving user.
		 * 
		 * message format: action:deliverMessage data:message/=username:
		 * test/,toGroupID
		 * /=1/,discussionParentID/=0/,fromUserID/=1/,status/=unread
		 * /,timeSend/=1305456522852
		 * /,toUserID/=0/,spam/=0/,subject/=/,toProjectID
		 * /=0/,toDiscussionID/=0/; key_value_delimiter:/=
		 * value_pair_delimiter:/, loginPassword:passwd ID:1
		 * passwordEncryption:clear text value_enclosed_by: data_format:array
		 * first_line_contains_keys:false
		 **/
		if (action.equals("deliverMessage")) {
			// 1. check if the user exists
			// if(user_inst.doesUserExist(loginUser, loginPassword,
			// passwordEncryption, database_auth_inst))

			communication com_inst = new communication();

			// LINES = deserializer_inst.deserialize(parameters);

			// String data = (String) parameters.get("data");
			String loginUser = (String) parameters.get("loginUser");
			String loginPassword = (String) parameters.get("loginUser");
			String pingRequestSendTime = "0"; // send this back to client so
												// client can calculate
												// roundtrip time

			Set<String> KEYSET = LINES.keySet();
			Object[] KEYS = KEYSET.toArray();

			HashMap<String, String> MESSAGE_HASH = (HashMap) LINES.get(0); // usually
																			// only
																			// one
																			// message
																			// is
																			// send
																			// at
																			// one
																			// time

			message MESSAGE_inst = new message(database_auth_inst);

			// message fromUserID toUserID status discussionParent subject
			// timeSend toGroup
			// {username=username, status=, ID=1, lastlogin=1302299541000,
			// loggedIn=1, openWindows=, IDlastReceivedMessage=4,
			// password=asdfjkl123!, pingRequestSendTime=1302299726888,
			// ping=1709}
			// System.out.println(MESSAGE_HASH.get("message"));
			MESSAGE_inst.message = MESSAGE_HASH.get("message");
			// System.out.println(MESSAGE_HASH.get("fromUserID"));
			MESSAGE_inst.fromUserID = MESSAGE_HASH.get("fromUserID");
			// System.out.println(MESSAGE_HASH.get("toUserID"));
			MESSAGE_inst.toUserID = MESSAGE_HASH.get("toUserID");
			// System.out.println(MESSAGE_HASH.get("toGroupID"));
			MESSAGE_inst.toGroupID = MESSAGE_HASH.get("toGroupID");
			// System.out.println(MESSAGE_HASH.get("toDiscussionID"));
			MESSAGE_inst.toDiscussionID = MESSAGE_HASH.get("toDiscussionID");
			// System.out.println(MESSAGE_HASH.get("toProjectID"));
			MESSAGE_inst.toProjectID = MESSAGE_HASH.get("toProjectID");
			// System.out.println(MESSAGE_HASH.get("status"));
			MESSAGE_inst.status = MESSAGE_HASH.get("status");
			// System.out.println(MESSAGE_HASH.get("spam"));
			MESSAGE_inst.spam = MESSAGE_HASH.get("spam");
			// System.out.println(MESSAGE_HASH.get("discussion"));
			MESSAGE_inst.DISCUSSION.name = MESSAGE_HASH.get("discussion");
			// System.out.println(MESSAGE_HASH.get("discussionParent"));
			MESSAGE_inst.DISCUSSION.discussionParentID = MESSAGE_HASH.get("discussionParentID");
			// System.out.println(MESSAGE_HASH.get("subject"));
			MESSAGE_inst.subject = MESSAGE_HASH.get("subject");
			// System.out.println(MESSAGE_HASH.get("timeSend"));
			MESSAGE_inst.timeSend = MESSAGE_HASH.get("timeSend");

			// 1. check if the sending user's credentials fit
			if(user_inst.doesUserExist(loginUser, loginPassword,passwordEncryption, database_auth_inst))
			{
				int MESSAGEID = com_inst.getMessageCount(database_auth_inst);

				// increment message count used as new ID for new message
				MESSAGEID++;

				MESSAGE_inst.MESSAGEID = Integer.toString(MESSAGEID);

				// 2. send message to user/group/project/discussion
				com_inst.sendMessage(MESSAGE_inst, database_auth_inst);

				com_inst.setMessageCount(MESSAGEID, database_auth_inst);
				answer.append("<record><status>success</status></record>\n");
				answer.append("<record><status>message delivered successfuly</status></record>\n");
				output.append(answer);
			}
			else
			{
				answer.append("<record><status>error</status></record>\n");
				answer.append("<record><status>can not send message, user does not exist</status></record>\n");
				output.append(answer);
			}
		}

		/**
		 * login -> check if given username & password are in the given table
		 * (default: users)
		 * 
		 * iterates through the list of loaded users & checks if there is a
		 * user with the given username and given password sends username +
		 * password to server server responds with LOGIN_RESULT = have a
		 * nice day! status = login success
		 * 
		 * LOGIN_RESULT = password did not match status = login failed
		 * 
		 * LOGIN_RESULT = user does not exis status = login failed
		 **/
		if (action.equals("login")) {

			/** handling credentials of the user of the program **/
			String loginUser = (String) parameters.get("loginUser");
			String loginPassword = (String) parameters.get("loginPassword");

			// 1. check if the user exists
			user_inst.doesUserExist(loginUser, loginPassword,
					passwordEncryption, database_auth_inst);

			if (user_inst.userExists.equals(false)) {
				answer.append("<record>");
				answer.append("<status><![CDATA[login failed]]></status>\n");
				answer.append("<LOGIN_RESULT><![CDATA[user does not exis]]></LOGIN_RESULT>\n");
				answer.append("</record>");
			} else {
				if (user_inst.passwordMatches) {
					Cookie COOKIE = new Cookie("together.cx", loginUser
							+ " has successfully logged in.");
					// output.append(DatabaseConnection.askDatabase( host,
					// DatabaseName, TableName, user, password, question,
					// "java"));
					// askDatabase(String host, String DatabaseName, String
					// TableName, String user, String password, String question)
					answer.append("<record>");
					answer.append("<status><![CDATA[login success]]></status>\n");
					answer.append("<LOGIN_RESULT><![CDATA[have a nice day!]]></LOGIN_RESULT>\n");
					answer.append("</record>");

					/**
					 * iterate over ArrayList (will most likely have only 1
					 * element, because only 1 user may exist with that
					 * username)
					 **/
					int LENGTH = user_inst.answerJAVA.size();
					for (int counter = 0; counter < LENGTH; counter++) {
						HashMap element = (HashMap) user_inst.answerJAVA.get(counter);

						/** iterate over hashMap via for loop by array-keyset **/
						Set<String> KEYSET = element.keySet();
						Object[] KEYS = KEYSET.toArray();

						/** iterate over answerJAVA **/
						answer.append("<record>\n");

						for (int subCounter = 0; subCounter < KEYS.length; ++subCounter) {
							if (KEYS[subCounter].equals("password")
									| KEYS[subCounter].equals("passwort")
									| KEYS[subCounter].equals("pwd")) {
								// answer.append("<"+KEYS[subCounter]+"><![CDATA[*masked*password*]]></"+KEYS[subCounter]+">\n");
								// // better to encrypt the whole url than to
								// mask passwords
								/**
								 * this is ONLY about the login of ONE use,
								 * where the password & username will/have to be
								 * send 1. from user to server (must, to auth,
								 * if this is over unencrypted connection... not
								 * good... TRY TO ENFORCE THE USAGE OF HTTPS AS
								 * DEFAULT!) 2. from server to user (backend
								 * just writes the user-data into a USER class
								 * instance, also writing the password.
								 */
								answer.append("<" + KEYS[subCounter]
										+ "><![CDATA["
										+ element.get(KEYS[subCounter])
										+ "]]></" + KEYS[subCounter] + ">\n");
							} else {
								answer.append("<" + KEYS[subCounter]
										+ "><![CDATA["
										+ element.get(KEYS[subCounter])
										+ "]]></" + KEYS[subCounter] + ">\n");
							}
						}
						answer.append("</record>\n");
					}
				} else {
					answer.append("<record>");
					answer.append("<status><![CDATA[login failed]]></status>\n");
					answer.append("<LOGIN_RESULT><![CDATA[password did not match]]></LOGIN_RESULT>\n");
					answer.append("</record>");
				}
			}

			output.append(answer);
		}

		/** readTable -> read the whole table and transmit it with one request/response **/
		if (action.equals("readTable")) {
			// ask the question
			if (utility_inst.empty(TableName))
			{
				try {
					// try to look for tablename differently
					// check how to process the result
					HashMap FIRSTELEMENT = (HashMap) LINES.get(0);
					TableName = (String) FIRSTELEMENT.get("TableName");
				} catch (Exception e) {
				}
			}

			if (!utility_inst.empty(TableName)) {
				question = "SELECT * FROM `" + database_auth_inst.DatabaseName +"`.`"+TableName+"`;";
				DatabaseConnection.askDatabase(database_auth_inst, question);
				// no further processing output data unfiltered

				if (DatabaseConnection.answerJAVA.size() == 0)
				{
					output.append("<record><status>empty result</status></record>\n");
				}
				else
				{
					output.append(DatabaseConnection.answerXML);
				}
			}
		}

		
		// readTable -> read the whole table and transmit it with one
		// request/response
		/**
		 * if there also comes data with this request, the data can contain
		 * informations on: what rows/columns should not be transmitted (e.g.
		 * confidental stuff when reading a whole table, e.g. passwords of other
		 * users etc.)
		 */
		if (action.equals("readTableFiltered")) {
			// check how to process the result
			HashMap FIRSTELEMENT = (HashMap) LINES.get(0);
			TableName = (String) FIRSTELEMENT.get("TableName");
			String doNotDeliverColumns = (String) FIRSTELEMENT.get("doNotDeliverColumns");

			String[] doNotDeliverColumns_array = strings_inst
					.stringSplit2Array(doNotDeliverColumns, ",");

			// ask the question
			question = "SELECT * FROM " + TableName;
			DatabaseConnection.askDatabase(database_auth_inst, question);

			// ask the question
			DatabaseConnection.askDatabase(database_auth_inst, question);

			// check for empty result
			if (DatabaseConnection.answerJAVA.size() == 0) {
				output
						.append("<record><status>empty result</status></record>\n");
			} else {
				/**
				 * iterate over ArrayList (will most likely have only 1 element,
				 * because only 1 user may exist with that username)
				 **/
				int LENGTH = DatabaseConnection.answerJAVA.size();
				for (int counter = 0; counter < LENGTH; counter++) {
					HashMap element = (HashMap) DatabaseConnection.answerJAVA.get(counter);

					/** iterate over hashMap via for loop by array-keyset **/
					Set<String> KEYSET = element.keySet();
					Object[] KEYS = KEYSET.toArray();

					// add status property to first element, to signal
					// success/error
					if (counter == 0) {
						answer.append("<record>\n<status><![CDATA[success]]></status>\n</record>\n");
					}

					/** iterate over answerJAVA **/
					answer.append("<record>\n");

					boolean inTheList = false;
					for (int subCounter = 0; subCounter < KEYS.length; ++subCounter) {
						int LENGTH1 = doNotDeliverColumns_array.length;
						inTheList = false;
						for (int counter1 = 0; counter1 < LENGTH1; counter1++) {
							if (KEYS[subCounter]
									.equals(doNotDeliverColumns_array[counter1])) {
								inTheList = true;
								break;
							}
						}
						if (inTheList) {
							// keep back this data to be transmitted over
							// potentially unsecure internet
							// connections/lines/proxies/servers
							// answer.append("<"+KEYS[subCounter]+"><![CDATA[***masked***]]></"+KEYS[subCounter]+">\n");
							// // better to encrypt the whole url than to mask
							// passwords
							answer.append("<" + KEYS[subCounter]
									+ "><![CDATA[***masked***]]></"
									+ KEYS[subCounter] + ">\n");
						} else {
							// deliver this data
							answer.append("<" + KEYS[subCounter] + "><![CDATA["
									+ element.get(KEYS[subCounter]) + "]]></"
									+ KEYS[subCounter] + ">\n");
						}
					}

					answer.append("</record>\n");
					output.append(answer);
				}
			}
		}

		/**
		 * Read only a part of a database-table from ID.... to ID
		 */
		if (action.equals("readRange")) {
			question = "SELECT * FROM `" + database_auth_inst.DatabaseName + "`.`" + TableName
					+ "`" + " LIMIT " + from + ", " + amount + ";";

			// ask the question
			DatabaseConnection.askDatabase(database_auth_inst, question);

			// check for empty result
			if (DatabaseConnection.answerJAVA.size() == 0) {
				output
						.append("<record><status>empty result</status></record>\n");
			} else {
				output.append(DatabaseConnection.answerXML);
			}
		}
		
		/** counts how many records are in a given table **/
		if (action.equals("count")) {
			question = "SELECT COUNT(*) FROM `" + TableName + "`";

			answer = DatabaseConnection.askDatabase(database_auth_inst,
					question);
			output.append(answer);
			// askDatabase(String host, String database_auth_inst.DatabaseName, String TableName,
			// String user, String password, String question)
		}

		/** write records/data into a table **/
		if (action.equals("insert")) {
			/** reads control_element properteis **/
			String data_format = (String) parameters.get("data_format"); // csv

			String first_line_contains_keys = (String) parameters.get("first_line_contains_keys"); // true or false
			String value_enclosed_by = (String) parameters.get("value_enclosed_by"); // " e.g.

			String value_pair_delimiter = (String) parameters.get("value_pair_delimiter"); // separates each key=value
													// pair
			String element_delimiter = (String) parameters.get("element_delimiter"); // separates each object-element
												// (containing multiple
												// key=value pairs)
			String key_value_delimiter = (String) parameters.get("key_value_delimiter"); // separates key from value in
													// this case "=" key=value

			String line_delimiter = (String) parameters.get("line_delimiter"); // "
																				// e.g.
			String values_delimiter = (String) parameters.get("values_delimiter"); // , e.g.
			String elements_per_line = (String) parameters.get("elements_per_line"); // ; e.g.

			LINES = null;

			if (data_format.equals("csv")) {
				csv CSV = new csv();
				LINES = CSV.csv2HashMap(data, elements_per_line, data_format,
						first_line_contains_keys, value_enclosed_by,
						values_delimiter, line_delimiter);
			}

			if (data_format.equals("array")) {
				LINES = deserializer_inst.deserialize(data);
			}

			/**
			 * when insert is used for user registration the seconary action is
			 * in action
			 **/
			/** assemble insert statement **/
			/**
			if (actionsSecondary != null) {
				if (actionsSecondary[0].equals("check if user allready exists")) {
					// handling credentials of the user of the program
					String loginUser = (String) parameters.get("loginUser");
					String loginPassword = (String) parameters.get("loginPassword");

					// 1. check if the user exists
					user_inst.doesUserExist(loginUser, loginPassword,
							passwordEncryption, database_auth_inst);
				}
			}
			**/

			question = "";
			String KEYS_STRING = "";
			String VALUES_STRING = "";

			LINES = deserializer_inst.deserialize(parameters);
			Set<String> KEYSET = LINES.keySet();
			Object[] KEYS = KEYSET.toArray();

			/** iterate over lines **/
			for (int counter = 0; counter < KEYS.length; counter++) {
				if (counter == 0) {
					// keys
					HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
					for (String ELEMENT : LINE.keySet()) {
						KEYS_STRING = KEYS_STRING + "`" + ELEMENT + "`" + ",";
					}
				}

				// values
				String TEMP_VALUES_STRING = " ( ";

				HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
				for (String ELEMENT : LINE.keySet()) {
					// ('2010-07-10 23:39:31', '2010-07-14 23:39:34',
					// '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
					// 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9',
					// '10', '11'),
					// ('2010-07-14 23:39:34', '2010-07-14 23:39:34',
					// '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
					// 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9',
					// '10', '11');
					TEMP_VALUES_STRING = TEMP_VALUES_STRING + "'"+ LINE.get(ELEMENT) + "'" + ",";
				}
				TEMP_VALUES_STRING = new String(TEMP_VALUES_STRING.substring(0,TEMP_VALUES_STRING.length() - 1)); // remove last ,
				VALUES_STRING = VALUES_STRING + TEMP_VALUES_STRING + " ) ,";
			}
			VALUES_STRING = new String(VALUES_STRING.substring(0,VALUES_STRING.length() - 1)); // remove last ,
			VALUES_STRING = VALUES_STRING + ";";

			KEYS_STRING = new String(KEYS_STRING.substring(0,KEYS_STRING.length() - 1)); // remove last ,

			question = "INSERT INTO `"+database_auth_inst.DatabaseName+"`.`"+TableName+"`("+KEYS_STRING+") VALUES " + VALUES_STRING;

			answer = DatabaseConnection.askDatabase(database_auth_inst, question);

			output.append(answer);
		}

		/** will insert a record into a table and return the all records of the table including the new record **/
		if (action.equals("insertReturnTable")) {
			
			/** reads control_element properteis **/
			String data_format = (String) parameters.get("data_format"); // csv
			
			String first_line_contains_keys = (String) parameters.get("first_line_contains_keys"); // true or false
			String value_enclosed_by = (String) parameters.get("value_enclosed_by"); // " e.g.
			
			String value_pair_delimiter = (String) parameters.get("value_pair_delimiter"); // separates each key=value
			// pair
			String element_delimiter = (String) parameters.get("element_delimiter"); // separates each object-element
			// (containing multiple
			// key=value pairs)
			String key_value_delimiter = (String) parameters.get("key_value_delimiter"); // separates key from value in
			// this case "=" key=value
			
			String line_delimiter = (String) parameters.get("line_delimiter"); // "
			// e.g.
			String values_delimiter = (String) parameters.get("values_delimiter"); // , e.g.
			String elements_per_line = (String) parameters.get("elements_per_line"); // ; e.g.
			
			LINES = null;
			
			if (data_format.equals("csv")) {
				csv CSV = new csv();
				LINES = CSV.csv2HashMap(data, elements_per_line, data_format,
						first_line_contains_keys, value_enclosed_by,
						values_delimiter, line_delimiter);
			}
			
			if (data_format.equals("array")) {
				LINES = deserializer_inst.deserialize(data);
			}
			
			/**
			 * when insert is used for user registration the seconary action is
			 * in action
			 **/
			/** assemble insert statement **/
			/**
			if (actionsSecondary != null) {
				if (actionsSecondary[0].equals("check if user allready exists")) {
					// handling credentials of the user of the program
					String loginUser = (String) parameters.get("loginUser");
					String loginPassword = (String) parameters.get("loginPassword");
					
					// 1. check if the user exists
					user_inst.doesUserExist(loginUser, loginPassword,
							passwordEncryption, database_auth_inst);
				}
			}
			**/
			
			question = "";
			String KEYS_STRING = "";
			String VALUES_STRING = "";
			
			LINES = deserializer_inst.deserialize(parameters);
			Set<String> KEYSET = LINES.keySet();
			Object[] KEYS = KEYSET.toArray();
			
			/** iterate over lines **/
			for (int counter = 0; counter < KEYS.length; counter++) {
				if (counter == 0) {
					// keys
					HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
					for (String ELEMENT : LINE.keySet()) {
						KEYS_STRING = KEYS_STRING + "`" + ELEMENT + "`" + ",";
					}
				}
				
				// values
				String TEMP_VALUES_STRING = " ( ";
				
				HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
				for (String ELEMENT : LINE.keySet()) {
					// ('2010-07-10 23:39:31', '2010-07-14 23:39:34',
					// '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
					// 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9',
					// '10', '11'),
					// ('2010-07-14 23:39:34', '2010-07-14 23:39:34',
					// '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
					// 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9',
					// '10', '11');
					TEMP_VALUES_STRING = TEMP_VALUES_STRING + "'"
					+ LINE.get(ELEMENT) + "'" + ",";
				}
				TEMP_VALUES_STRING = new String(TEMP_VALUES_STRING.substring(0,
						TEMP_VALUES_STRING.length() - 1)); // remove last ,
				VALUES_STRING = VALUES_STRING + TEMP_VALUES_STRING + " ) ,";
			}
			VALUES_STRING = new String(VALUES_STRING.substring(0,
					VALUES_STRING.length() - 1)); // remove last ,
			VALUES_STRING = VALUES_STRING + ";";
			
			KEYS_STRING = new String(KEYS_STRING.substring(0,
					KEYS_STRING.length() - 1)); // remove last ,
			
			question = "INSERT INTO `" + database_auth_inst.DatabaseName + "`.`" + TableName
			+ "` ( " + KEYS_STRING + " ) VALUES " + VALUES_STRING;
			
			answer = DatabaseConnection.askDatabase(database_auth_inst,question);

			// read the whole table after insert
			answer = DatabaseConnection.askDatabase(database_auth_inst,"SELECT * FROM `"+database_auth_inst.DatabaseName+"`.`"+TableName+"`;");
			
			output.append(answer);
		}

		/** insert data only if the data not already exists in database **/
		if (action.equals("insertIfNotExists"))
		{
			/** reads control_element properties **/
			String data_format = (String) parameters.get("data_format"); // csv

			String first_line_contains_keys = (String) parameters.get("first_line_contains_keys"); // true or false
			String value_enclosed_by = (String) parameters.get("value_enclosed_by"); // " e.g.
			String value_pair_delimiter = (String) parameters.get("value_pair_delimiter"); // separates each key=value pair
			String element_delimiter = (String) parameters.get("element_delimiter"); // separates each object-element (containing multiple key=value pairs)
			String key_value_delimiter = (String) parameters.get("key_value_delimiter"); // separates key from value in this case "=" key=value
			String line_delimiter = (String) parameters.get("line_delimiter");
			String values_delimiter = (String) parameters.get("values_delimiter"); // , e.g.
			String elements_per_line = (String) parameters.get("elements_per_line"); // ; e.g.
			String data = (String) parameters.get("data"); //

			/** handling authentication data of the user that wants to register **/
			String loginUser = (String) parameters.get("loginUser");
			String loginPassword = (String) parameters.get("loginPassword");

			LINES = null;

			if(data_format.equals("array"))
			{
				LINES = deserializer_inst.deserialize(data);
			}

			/**
			 * when insert is used for user registration the seconary action is
			 * in action
			 **/
			/** assemble insert statement **/
			// 1. check if the record/entry exists
			if (DatabaseConnection.exists(database_auth_inst, TableName, data).equals(false))
			{
				question = "";
				String KEYS_STRING = "";
				String VALUES_STRING = "";

				Set<String> KEYSET = LINES.keySet();
				Object[] KEYS = KEYSET.toArray();

				/** iterate over lines **/
				for(int counter = 0; counter < KEYS.length; counter++)
				{
					if(counter == 0)
					{
						// keys
						HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
						for (String ELEMENT : LINE.keySet())
						{
							KEYS_STRING = KEYS_STRING + "`" + ELEMENT + "`,";
						}
					}

					// values
					String TEMP_VALUES_STRING = " ( ";

					HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
					for (String ELEMENT : LINE.keySet())
					{
						TEMP_VALUES_STRING = TEMP_VALUES_STRING + "'"+ LINE.get(ELEMENT) + "'" + ",";
					}
					TEMP_VALUES_STRING = new String(TEMP_VALUES_STRING.substring(0,TEMP_VALUES_STRING.length() - 1)); // remove last ,
					VALUES_STRING = VALUES_STRING + TEMP_VALUES_STRING + " ) ,";
				}
				VALUES_STRING = new String(VALUES_STRING.substring(0,VALUES_STRING.length() - 1)); // remove last ,
				VALUES_STRING = VALUES_STRING + ";";

				KEYS_STRING = new String(KEYS_STRING.substring(0,KEYS_STRING.length() - 1)); // remove last ,

				/**
				 * "INSERT INTO `"+DatabaseName+"`.`"+TableName+"` (" `ID` ,
				 * `kunde_geworben_am` , `erfasst_am` , `nachtelefoniert_am` ,
				 * `abschlus_des_gesch�ftes_am` , `kundennummer` ,
				 * `handelsvertreter` , `maschinenring` , `kunden_name` ,
				 * `kunden_ort` , `kunden_tel` , `kunden_email` , `netto` ,
				 * `kwp` , `prozent_vc` , `bemerkung` , `priority` ) VALUES
				 * (NULL , '2010-07-10 23:39:31', '2010-07-14 23:39:34',
				 * '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
				 * 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10',
				 * '11'), (NULL , '2010-07-14 23:39:34', '2010-07-14 23:39:34',
				 * '2010-07-14 23:39:34', '2010-07-14 23:39:34', '2010-07-14
				 * 23:39:34', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10',
				 * '11');
				 **/
				question = "INSERT INTO `" + database_auth_inst.DatabaseName + "`.`" + TableName+ "` ( " + KEYS_STRING + " ) VALUES " + VALUES_STRING;

				DatabaseConnection.askDatabase(database_auth_inst, question);

				if(DatabaseConnection.success)
				{
					answer.append("<record>");
					answer.append("<status><![CDATA[insertIfNotExists successful]]></status>\n");
					answer.append("<error><![CDATA["+DatabaseConnection.status+"]]></error>\n");
					answer.append("<RESULT><![CDATA[element does not exist yet]]></RESULT>\n");
					answer.append("</record>");
				}
				else
				{
					answer.append("<record>");
					answer.append("<status><![CDATA[insertIfNotExists failed]]></status>\n");
					answer.append("<error><![CDATA["+DatabaseConnection.status+"]]></error>\n");
					answer.append("<RESULT><![CDATA["+DatabaseConnection.status+"]]></RESULT>\n");
					answer.append("</record>");
				}
			}
			else
			{
				answer.append("<record>");
				answer.append("<status><![CDATA[insertIfNotExists failed]]></status>\n");
				answer.append("<error><![CDATA["+DatabaseConnection.status+"]]></status>\n");
				answer.append("<RESULT><![CDATA[element does exist]]></RESULT>\n");
				answer.append("</record>");
			}

			output.append(answer);
		}

		/** register a new user **/
		if (action.equals("register")) {
			/** reads control_element properteis **/
			String data_format = (String) parameters.get("data_format"); // csv

			String groups = "";

			String first_line_contains_keys = (String) parameters.get("first_line_contains_keys"); // true or false
			String value_enclosed_by = (String) parameters.get("value_enclosed_by"); // " e.g.

			String value_pair_delimiter = (String) parameters.get("value_pair_delimiter"); // separates each key=value
													// pair
			String element_delimiter = (String) parameters.get("element_delimiter"); // separates each object-element
												// (containing multiple
												// key=value pairs)
			String key_value_delimiter = (String) parameters.get("key_value_delimiter"); // separates key from value in
													// this case "=" key=value

			String line_delimiter = (String) parameters.get("line_delimiter"); // "
																				// e.g.
			String values_delimiter = (String) parameters.get("values_delimiter"); // , e.g.
			String elements_per_line = (String) parameters.get("elements_per_line"); // ; e.g.
			// String data = (String) parameters.get("data"); //

			/** handling authentication data of the user that wants to register **/
			String loginUser = (String) parameters.get("loginUser");
			String loginPassword = (String) parameters.get("loginPassword");

			LINES = null;

			if(data_format.equals("array"))
			{
				LINES = deserializer_inst.deserialize(data);
			}

			/**
			 * when insert is used for user registration the seconary action is
			 * in action
			 **/
			/** assemble insert statement **/
			// 1. check if the user exists
			user_inst.doesUserExist(loginUser, loginPassword, passwordEncryption, database_auth_inst);

			if (user_inst.userExists.equals(false))
			{
				question = "";
				String KEYS_STRING = "";
				String VALUES_STRING = "";

				Set<String> KEYSET = LINES.keySet();
				Object[] KEYS = KEYSET.toArray();

				/** iterate over lines **/
				for (int counter = 0; counter < KEYS.length; counter++)
				{
					if (counter == 0)
					{
						// keys
						HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
						for (String ELEMENT : LINE.keySet())
						{
							if (!ELEMENT.equals("ID")) // ID is automatically generated by server (mysql auto increment)
							{
								if (!ELEMENT.equals("groups")) // group handling will be taken care of later
								{
									KEYS_STRING = KEYS_STRING + "`" + ELEMENT+ "`" + ",";
								}
							}
						}
					}

					// values
					String TEMP_VALUES_STRING = " ( ";

					HashMap<String, String> LINE = (HashMap) LINES.get(KEYS[counter]); // cast to hashmap
					for (String ELEMENT : LINE.keySet())
					{
						if (!ELEMENT.equals("ID"))
						{
							if (ELEMENT.equals("groups")) // group handling will be taken care of later
							{
								groups = ELEMENT;
							}
							else
							{
								TEMP_VALUES_STRING = TEMP_VALUES_STRING + "'"+ LINE.get(ELEMENT) + "'" + ",";
							}
						}
					}
					TEMP_VALUES_STRING = new String(TEMP_VALUES_STRING.substring(0,TEMP_VALUES_STRING.length() - 1)); // remove last ,
					VALUES_STRING = VALUES_STRING + TEMP_VALUES_STRING + " ) ,";
				}
				VALUES_STRING = new String(VALUES_STRING.substring(0,VALUES_STRING.length() - 1)); // remove last ,
				VALUES_STRING = VALUES_STRING + ";";

				KEYS_STRING = new String(KEYS_STRING.substring(0,
						KEYS_STRING.length() - 1)); // remove last ,

				// 1. try to create the user
				question = "INSERT INTO `" + database_auth_inst.DatabaseName + "`.`"+ database_auth_inst.authTable + "` ( " + KEYS_STRING+ " ) VALUES " + VALUES_STRING;

				DatabaseConnection.askDatabase(database_auth_inst, question);

				if (DatabaseConnection.success) {
					// 2. try to create the default groups for that user
					// 2.1. need to get its USERID first
					String USERID = user_inst.getID(loginUser, loginPassword,passwordEncryption, database_auth_inst);

					// if user was registered successfully, add default groups
					groups_inst.newGroup("family", USERID,"users of my family", database_auth_inst);
					groups_inst.newGroup("blockedUser", USERID,"users i wish to ignore", database_auth_inst);
					groups_inst.newGroup("friends", USERID,"users i wish to remember and chat with often",database_auth_inst);

					answer.append("<record>");
					answer.append("<status><![CDATA[user registration successful]]></status>\n");
					answer.append("<RESULT><![CDATA[user did not exist yet]]></RESULT>\n");
					answer.append("</record>");
				} else {
					System.err.println("Error: " + DatabaseConnection.status);
					answer.append("<record>");
					answer.append("<status><![CDATA[user registration failed]]></status>\n");
					answer.append("<ERROR><![CDATA["+ DatabaseConnection.status + "]]></ERROR>\n");
					answer.append("<MYSQLERROR><![CDATA["+ DatabaseConnection.status + "]]></MYSQLERROR>\n");
					answer.append("<RESULT><![CDATA[element does exist]]></RESULT>\n");
					answer.append("</record>");
				}
			} else {
				answer.append("<record>");
				answer.append("<status><![CDATA[user registration failed]]></status>\n");
				answer.append("<RESULT><![CDATA[element does exist]]></RESULT>\n");
				answer.append("</record>");
			}

			output.append(answer);
		}

		/** update/change records in a table **/
		if (action.equals("update")) {
			// read http request arguments/parameters
			enumeration = request.getParameterNames();
			while (enumeration.hasMoreElements()) {
				String name = (String) enumeration.nextElement();
				String value = (String) request.getParameter(name);
				// String values[] = request.getParameterValues(name); //
				// Returns an array of String objects containing all of the
				// values the given request parameter has, or null if the
				// parameter does not exist.

				parameters.put(name, value); // adding value into HashMap

				// if (values != null) {
				// for (int i = 0; i < values.length; i++) {
				// System.out.println(name + " (" + i + "): " + values[i]);
				// parameters.put( name, values ); // adding value into HashMap
				// }
				// }
			}

			// question = "SELECT * FROM "+TableName;
			// output.append(DatabaseConnection.askDatabase(
			// database_auth_inst, question ));
			// askDatabase(String host, String DatabaseName, String TableName,
			// String user, String password, String question)
		}

		output.append("</sqlxml>");
		writer.println(output);
		writer.close();
	}
}
